﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.Mapper;
using QuanLyGiaoVuDAL.DTO;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class SinhVienRepository:ISinhVienRepository
    {
        public SinhVienDTO GetSinhVienById(Guid sinhVienId)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("ID", sinhVienId);
            parameters[1] = DBParameterFactory.Create("MaSinhVien", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienSelect]", parameters);
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToSinhVienDTO();
            }
            catch (Exception ex)
            {
                return null;
            }          
        }
        public SinhVienDTO GetSinhVienByMSSV(String MaSinhVien)
        {
            SqlParameter[] parameters = new SqlParameter[2];
            parameters[0] = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = DBParameterFactory.Create("MaSinhVien", MaSinhVien);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienSelect]", parameters);
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToSinhVienDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public SinhVienDTO UpdateSinhVien(QuanLyGiaoVuDAL.DTO.SinhVienDTO sinhVienDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienUpdate]", GetParameterArray(sinhVienDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToSinhVienDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public SinhVienDTO InsertSinhVien(QuanLyGiaoVuDAL.DTO.SinhVienDTO sinhVienDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienInsert]", GetParameterArray(sinhVienDTO));
            try
            {
                var dataRow = dbResult.Tables[0].Rows[0];
                return dataRow.ToSinhVienDTO();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public bool DeleteSinhVien(Guid sinhVienId)
        {            
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienDelete]", DBParameterFactory.Create("@ID", sinhVienId));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        #region Private Methods
        private System.Data.SqlClient.SqlParameter[] GetParameterArray(SinhVienDTO sinhVienDTO)
        {
            SqlParameter[] parameters = new SqlParameter[11];
            parameters[0] = DBParameterFactory.Create("ID", sinhVienDTO.ID);
            parameters[1] = DBParameterFactory.Create("MaSinhVien", sinhVienDTO.MaSinhVien);
            parameters[2] = DBParameterFactory.Create("HoTenSV", sinhVienDTO.HoTenSV);
            parameters[3] = DBParameterFactory.Create("GioiTinh", sinhVienDTO.GioiTinh);
            parameters[4] = DBParameterFactory.Create("NgaySinh", sinhVienDTO.NgaySinh);
            parameters[5] = DBParameterFactory.Create("QueQuan", sinhVienDTO.QueQuan);
            parameters[6] = DBParameterFactory.Create("DiaChi", sinhVienDTO.DiaChi);
            parameters[7] = DBParameterFactory.Create("Email", sinhVienDTO.Email);
            parameters[8] = DBParameterFactory.Create("MaLop", sinhVienDTO.MaLop);
            parameters[9] = DBParameterFactory.Create("LopTruong", sinhVienDTO.LopTruong);
            parameters[10] = DBParameterFactory.Create("DuDKLuanVan", sinhVienDTO.DuDKLuanVan);
            return parameters;
        }
        #endregion


        public IEnumerable<SinhVienDTO> GetAllSinhVien(Guid? lopSinhVienId, bool? duDKLamLuanVan)
        {
            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = DBParameterFactory.Create("ID", SqlDbType.UniqueIdentifier, DBNull.Value);
            parameters[1] = DBParameterFactory.Create("MaSinhVien", SqlDbType.NVarChar, DBNull.Value);
            parameters[2] = duDKLamLuanVan != null ? DBParameterFactory.Create("DuDKLuanVan", SqlDbType.Bit, duDKLamLuanVan) : DBParameterFactory.Create("DuDKLuanVan", SqlDbType.Bit, DBNull.Value);
            parameters[3] = lopSinhVienId.HasValue ? DBParameterFactory.Create("LopSinhVien", lopSinhVienId) : DBParameterFactory.Create("LopSinhVien", SqlDbType.UniqueIdentifier, DBNull.Value);

            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_SinhVienSelect]", parameters);

            IList<SinhVienDTO> dsSinhVien = new List<SinhVienDTO>();
            try
            {
                var dataRows = dbResult.Tables[0].Rows;
                foreach (DataRow row in dataRows)
                    dsSinhVien.Add(row.ToSinhVienDTO());
                return dsSinhVien;
            }
            catch (Exception ex)
            {
                return dsSinhVien;
            }          
        }
    }
}
